<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>DataSet</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="Visual Basic, SQLite, DataGridView, SqliteDataAdapter, DataSet, DataTable, ADO.NET">
<meta name="description" content="In this chapter of the SQLite Visual Basic tutorial, we work with
    the DataSet, DataTable, SqliteDataAdapter and DataGridView classes.">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

</head>

<body>

<div class="container2">

<div id="wide_ad" class="ltow">

<div>
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

</div>


<div class="content2">

<a href="/" title="Home">Home</a>&nbsp;
<a href="..">Contents</a>


<h1>DataSet</h1>


<p>
The ADO.NET architecture consists of two central parts. The .NET Data Providers
and the DataSet. The data providers are components that have been explicitly designed 
for data manipulation and fast access to data. 
The DataSet is created for data access independent of any data source. 
It can be used with multiple and differing data sources, with XML data, or used to 
manage data local to the application.
</p>

<div class="big_hor">
<script type="text/javascript"><!--
google_ad_client = "ca-pub-9706709751191532";
/* big_horizontal */
google_ad_slot = "2904953388";
google_ad_width = 728;
google_ad_height = 90;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<p>
A <b>DataSet</b> is a copy of the data and
the relations among the data from the database tables. It is created
in memory and used when extensive processing on data is needed or 
when we bind data tables to a Winforms control. When the processing is
done, the changes are written to the data source. The DataSet is a disconnected
relational structure. This means that the underlying connection does not have to be open 
during the entire life of a DataSet object. This enables us to use efficiently our
available database connections. 
</p>

<p>
A DataSet can be populated in a variety of ways. We can use the Fill() method
of the SqliteDataAdapter class. We can create programmatically
the DataTable, DataColumn and DataRow objects. Data can be read
from an XML document or from a stream. 
</p>

<p>
A <b>SqliteDataAdapter</b> is an intermediary
between the DataSet and the data source. It populates a DataSet 
and resolves updates with the data source. A <b>DataTable</b> is a 
representation of a database table in a memory. One or more data tables
may be added to a data set. The changes made to the DataSet are saved to
data source by the <b>SqliteCommandBuilder</b> class. 
</p>

<p>
The <b>DataGridView</b> control provides a customizable table for displaying data. 
It allows customization of cells, rows, columns, and borders 
through the use of properties. We can use this control to display data 
with or without an underlying data source. 
</p>


<h2>Creating a DataTable</h2>

<p>
In the first example, we will work with the DataTable class. 
</p>

<pre>
sqlite> CREATE TABLE Friends2(Id INTEGER PRIMARY KEY, Name TEXT);
</pre>

<p>
In this case, the table must be created before we can save any data into it.
</p>

<pre class="code">
Option Strict On

Imports Mono.Data.Sqlite
Imports System.Data

Module Example

    Sub Main()

        Dim cs As String = "URI=file:test.db"

        Using con As New SqliteConnection(cs)
        
            con.Open()
        
            Dim table As New DataTable("Friends2")

            Dim column As New DataColumn
            Dim row As DataRow
 
            column = New DataColumn()
            column.DataType = System.Type.GetType("System.Int32")
            column.ColumnName = "Id"
            table.Columns.Add(column)

            column = new DataColumn()
            column.DataType = Type.GetType("System.String")
            column.ColumnName = "Name"
            table.Columns.Add(column)

            row = table.NewRow()
            row("Id") = 1
            row("Name") = "Jane"
            table.Rows.Add(row)

            row = table.NewRow()
            row("Id") = 2
            row("Name") = "Lucy"
            table.Rows.Add(row)

            row = table.NewRow()
            row("Id") = 3
            row("Name") = "Thomas"
            table.Rows.Add(row)

            Dim sql As String = "SELECT * FROM Friends2"

            Using da As New SqliteDataAdapter(sql, con)             
                Using cb As New SqliteCommandBuilder(da)                
                    da.Fill(table)
                    da.Update(table)
                End Using
            End Using

            con.Close()

        End Using
        
    End Sub

End Module
</pre>

<p>
In the example, we create a new DataTable object. We add two columns and
three rows to the table. Then we save the data in a new Friends2 database
table. 
</p>

<pre class="explanation">
Dim table As New DataTable("Friends2")
</pre>

<p>
A new DataTable object is created. 
</p>

<pre class="explanation">
column = New DataColumn()
column.DataType = System.Type.GetType("System.Int32")
column.ColumnName = "Id"
table.Columns.Add(column)
</pre>

<p>
A new column is added to the table. We provide a data type and
name for the column. The columns of a DataTable
are accesed via the <code>Columns</code> property. 
</p>

<pre class="explanation">
row = table.NewRow()
row("Id") = 1
row("Name") = "Jane"
table.Rows.Add(row)
</pre>

<p>
A row is added to the table. The rows of a DataTable
are accesed via the <code>Rows</code> property. 
</p>

<pre class="explanation">
Dim sql As String = "SELECT * FROM Friends2"

Using da As New SqliteDataAdapter(sql, con)  
</pre>

<p>
The <code>SqliteDataAdapter</code> is an intermediary between the
database table and its representation in the memory. 
</p>

<pre class="explanation">
Using cb As New SqliteCommandBuilder(da)
</pre>

<p>
The <code>SqliteCommandBuilder</code> wraps the data adapter. 
It only needs to be instantiated. We do not work with it directly
later. 
</p>

<pre class="explanation">
da.Fill(table)
da.Update(table)
</pre>

<p>
The data adapter is filled with the data from the table. The
<code>Update</code> method inserts the data to the database.
</p>


<h2>Saving XML data</h2>

<p>
Data from the DataTable can be easily saved in an XML file.
There is a <code>WriteXml()</code> method for this task.
</p>

<pre class="code">
Option Strict On

Imports Mono.Data.Sqlite
Imports System.Data

Module Example

    Sub Main()

        Dim cs As String = "URI=file:test.db"

        Using con As New SqliteConnection(cs)        
            con.Open()

            Dim stm As String = "SELECT * FROM Cars LIMIT 5"
        
            Using da As New SqliteDataAdapter(stm, con)
                Dim ds As New DataSet()
                
                da.Fill(ds, "Cars")
                Dim dt As DataTable = ds.Tables("Cars")

                dt.WriteXml("cars.xml")

                For Each row As DataRow In dt.Rows                         
                    For Each col As DataColumn In dt.Columns                    
                        Console.Write(row(col) &amp; " ")
                    Next
                    Console.WriteLine()
                Next      
            End Using
            con.Close()
        End Using
        
    End Sub

End Module
</pre>

<p>
We print 5 cars from the Cars table. We also save them
in an XML file.
</p>

<pre class="explanation">
Using da As New SqliteDataAdapter(stm, con)
</pre>

<p>
A <code>SqliteDataAdapter</code> object is created. It takes an SQL 
statement and a connection as parameters. The SQL statement will be used
to retrieve and pass the data by the SqliteDataAdapter. 
</p>

<pre class="explanation">
Dim ds As New DataSet()

da.Fill(ds, "Cars")
</pre>

<p>
We create the <code>DataSet</code> object. The <code>Fill()</code> method
uses the data adapter to retrieve the data from the data source. It creates
a new DataTable named Cars and fills it with the retrieved data.  
</p>

<pre class="explanation">
Dim dt As DataTable = ds.Tables("Cars")
</pre>

<p>
The <code>Tables</code> property provides us with the
collection of data tables contained in the DataSet. 
From this collection we retrieve the Cars DataTable. 
</p>

<pre class="explanation">
dt.WriteXml("cars.xml")
</pre>

<p>
We write the data from the data table to an XML file. 
</p>

<pre class="explanation">
For Each row As DataRow In dt.Rows                         
    For Each col As DataColumn In dt.Columns                    
        Console.Write(row(col) &amp; " ")
    Next
    Console.WriteLine()
Next    
</pre>

<p>
We display the contents of the Cars table to the
terminal. To traverse the data, we utilize the rows and
columns of the DataTable object. 
</p>



<h2>Loading XML data</h2>

<p>
We have shown how to save data in XML files. Now we are going 
to show, how to load the data from an XML file. 
</p>

<pre class="code">
Option Strict On

Imports Mono.Data.Sqlite
Imports System.Data

Module Example

    Sub Main()

        Dim cs As String = "URI=file:test.db"

        Using con As New SqliteConnection(cs)   
     
            con.Open()

            Dim ds As New DataSet()
            
            ds.ReadXml("cars.xml")
            Dim dt As DataTable = ds.Tables("Cars")

            For Each row As DataRow In dt.Rows                        
                For Each col As DataColumn In dt.Columns                
                    Console.Write(row(col) + " ")
                Next
                Console.WriteLine()
            Next

            con.Close()
        End Using
        
    End Sub

End Module
</pre>

<p>
We read the contents of the cars.xml file into the DataSet. 
We print all the rows to the terminal. 
</p>

<pre class="explanation">
Dim ds As New DataSet()
</pre>

<p>
A DataSet object is created. 
</p>

<pre class="explanation">
ds.ReadXml("cars.xml")
</pre>

<p>
The data from the cars.xml is read into the data set. 
</p>

<pre class="explanation">
Dim dt As DataTable = ds.Tables("Cars")
</pre>

<p>
When the data was read into the data set a new DataTable
was created. We get this table.
</p>

<pre class="explanation">
For Each row As DataRow In dt.Rows                        
    For Each col As DataColumn In dt.Columns                
        Console.Write(row(col) + " ")
    Next
    Console.WriteLine()
Next 
</pre>

<p>
We print all the rows of the data table. 
</p>

<pre>
$ mono loadxml.exe 
1 Audi 52642 
2 Mercedes 57127 
3 Skoda 9000 
4 Volvo 29000 
5 Bentley 350000
</pre>

<p>
Running the example.
</p>


<h2>DataGridView</h2>

<p>
In the next example, we are going to bind a table to 
a Winforms DataGridView control.
</p>

<pre class="code">
Option Strict On

Imports System.Windows.Forms
Imports System.Drawing
Imports System.Data
Imports Mono.Data.Sqlite


Public Class WinVBApp
    Inherits Form

    Private dgv As DataGridView
    Private da As SqliteDataAdapter
    Private ds As DataSet

    Public Sub New()

       Me.Text = "DataGridView"
       Me.Size = New Size(450, 350)
       
       Me.InitUI()
       Me.InitData()
       
       Me.CenterToScreen()

    End Sub
    
    Private Sub InitUI()

        dgv = New DataGridView()

        dgv.Location = New Point(8, 0)
        dgv.Size = New Size(350, 300)
        dgv.TabIndex = 0
        dgv.Parent = Me 

    End Sub


    Private Sub InitData()
        
        Dim cs As String = "URI=file:test.db"

        Dim con As New SqliteConnection(cs)
    
        Dim stm As String = "SELECT * FROM Cars"

        Using con As New SqliteConnection(cs)
            con.Open()

            ds = new DataSet()

            Using da As New SqliteDataAdapter(stm, con)            
                da.Fill(ds, "Cars")
                dgv.DataSource = ds.Tables("Cars")
            End Using
            
            con.Close()
        End Using   

    End Sub

    Public Shared Sub Main()
        Application.Run(New WinVBApp)
    End Sub

End Class
</pre>

<p>
In this example, we bind the Cars table to the Winforms
<code>DataGridView</code> control.
</p>

<pre class="explanation">
Imports System.Windows.Forms
Imports System.Drawing
</pre>

<p>
These two namespaces are for the GUI. 
</p>

<pre class="explanation">
Me.InitUI()
Me.InitData()
</pre>

<p>
Inside the InitUI() method, we build the user interface. 
In the InitData() method, we connect to the database, retrieve
the data into the DataSet and bind it to the DataGrid control. 
</p>

<pre class="explanation">
dgv = New DataGridView()
</pre>

<p>
The <code>DataGridView</code> control is created. 
</p>

<pre class="explanation">
Dim stm As String = "SELECT * FROM Cars"
</pre>

<p>
We will display the data from the Cars table in the DataGridView control.
</p>

<pre class="explanation">
dgv.DataSource = ds.Tables("Cars")
</pre>

<p>
We bind the <code>DataSource</code> property of the DataGridView control to the
chosen table. 
</p>

<img src="/img/db/sqlitevb/datagridview.png" alt="DataGridView">
<div class="figure">Figure: DataGridView</div>

<p>
In this part of the SQLite Visual Basic tutorial, we have worked with the DataSet, DataTable, 
SqliteDataAdapter, SqliteCommandBuilder and DataGridView classes. 
</p>


<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="..">Contents</a></span> ‡ 
<span class="botNavItem"><a href="#">Top of Page</a></span>
</div>


<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified April 28, 2012  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>

